﻿IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'usp_UPDMS_ETC_REPORT_Etc2010r_Get_Total_Lego_Report')
	BEGIN
		DROP Procedure usp_UPDMS_ETC_REPORT_Etc2010r_Get_Total_Lego_Report
	END
GO

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
/******************************************************************************
**	Name : usp_UPDMS_ETC_REPORT_Etc2010r_Get_Total_Lego_Report
**	Desc : 레고관리를 조회한다.
**	Test Exec Query : Exec usp_UPDMS_ETC_REPORT_Etc2010r_Get_Total_Lego_Report 'ko'
**	Called by : Etc_Dac_UPDMS_ETC_LEGO_MGT.cs
**	Program ID : Etc201or
**	Auth : 송시명
**	Date : 2014-09-15
*******************************************************************************
**	Change History
*******************************************************************************
**	Date:		Author:		Description:
**	--------	--------	---------------------------------------
**			
*******************************************************************************/
CREATE PROC [dbo].[usp_UPDMS_ETC_REPORT_Etc2010r_Get_Total_Lego_Report]
@ls_lang_set nvarchar(2)
AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON

SELECT dbo.ufn_UPDMS_Get_Code_Name(z.Series, 'ETC', @ls_lang_set) AS Series_Nm,
       Total_Cnt,
       Total_Price,
       Total_Brick_Cnt,
       Total_Brick_Weight,
       Total_Figure_Cnt
  FROM (
       SELECT Series,
              COUNT(Seq) AS Total_Cnt,
              SUM(Price) AS Total_Price,
              SUM(Brick_Cnt) AS Total_Brick_Cnt,
              SUM(Brick_Weight) AS Total_Brick_Weight,
              SUM(Figure_Cnt) AS Total_Figure_Cnt
         FROM UPDMS_ETC_LEGO_MGT WITH(NOLOCK)
        GROUP BY Series
       ) z


--요일별 구입수
SELECT z.Week_Day,
       SUM(ISNULL(z.Total_Cnt, 0)) AS Total_Cnt,
       SUM(ISNULL(z.Total_Price, 0)) AS Total_Price
  FROM (
       SELECT umd.Dt as Buy_Dt,
              CASE @ls_lang_set WHEN 'ko' THEN umd.Day_K_Abb_Nm ELSE umd.Day_E_Abb_Nm END Week_Day,
              urt.Total_Cnt,
              urt.Total_Price,
              umd.View_Order
         FROM UPDMS_MST_DATE umd WITH(NOLOCK)
         LEFT OUTER JOIN
		      (
              SELECT COUNT(Seq) AS Total_Cnt,
                     SUM(Price) AS Total_Price,
                     Buy_Dt
                FROM UPDMS_ETC_LEGO_MGT WITH(NOLOCK)
               GROUP BY Buy_Dt
              ) urt
           ON umd.Dt = urt.Buy_Dt
       ) z
 GROUP BY z.Week_Day
 ORDER BY MAX(z.View_Order)


--최근구입정보
SELECT ROW_NUMBER() OVER(ORDER BY Series, Buy_Dt) AS Rnum,
       Series_Nm,
       Prod_Nm,
       CASE Partition_Num WHEN 1 THEN '<b class="b">' + CONVERT(NVARCHAR, Cnt) + '</b>' ELSE CONVERT(NVARCHAR, Cnt) END Cnt,
       Buy_Dt
  FROM (
       SELECT Series,
              Series_Nm,
              Prod_Nm,
              DATEDIFF(DAY, CONVERT(DATETIME, Buy_Dt), GETDATE()) AS Cnt,
              Buy_Dt,
              Partition_Num = ROW_NUMBER() OVER(PARTITION BY Series ORDER BY Buy_Dt DESC)
         FROM (
              SELECT Series,
                     dbo.ufn_UPDMS_Get_Code_Name(Series, 'ETC', @ls_lang_set) AS Series_Nm,
                     Prod_Nm,
                     Buy_Dt
                FROM UPDMS_ETC_LEGO_MGT WITH(NOLOCK)
              ) A
       ) B
 WHERE Partition_Num <= CONVERT(INT, (dbo.ufn_UPDMS_Get_Setting_Value('ETC_Opt_01')))
 

--년도별 구입수,구입가
SELECT YEAR(CONVERT(DATETIME, Buy_Dt)) AS Buy_Year,
       COUNT(Seq) AS Total_Cnt,
       SUM(Price) AS Total_Price
  FROM UPDMS_ETC_LEGO_MGT WITH(NOLOCK)
 GROUP BY YEAR(CONVERT(DATETIME, Buy_Dt))
 ORDER BY YEAR(CONVERT(DATETIME, Buy_Dt))

GO